﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace POS
{
    class DataTablesClass
    {

        public static string myConnectionString = @"Data Source=ROGERJENKINS-DT\SQLEXPRESS;Initial Catalog=RabbitSoftware;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
        public static DataTable Receipts = new DataTable("Receipts");
        public static DataTable ReceiptsData = new DataTable("ReceiptData");
        public static DataTable MenuItems = new DataTable("MenuItems");
        public static DataTable MenuItemsCategory = new DataTable("MenuItemsCategory");
        public static DataTable EmployeeLogin;// = new DataTable();
        public static DataTable EmployeeTimeSheet = new DataTable("EmployeeTimeSheet");
        public static DataTable EmployeeTips = new DataTable("EmployeeTips");
        public static DataTable EmployeeSchedule = new DataTable("EmployeeSchedule");

        public static List<string> TableNames = new List<string>();
        public static DataSet POS = new DataSet();
        public static Dictionary<string, double> dictionaryPrices = new Dictionary<string, double>();
        public static Dictionary<string, string> dictionaryCategorys = new Dictionary<string, string>();
        public static List<string> categoryList = new List<string>();

        public static void GetTables()//List<string> GetTables()//string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(myConnectionString))
            {
                connection.Open();
                DataTable schema = connection.GetSchema("Tables");
                
                foreach (DataRow row in schema.Rows)
                {

                    TableNames.Add(row[2].ToString());
                    MessageBox.Show(row[1].ToString());
                }
                MessageBox.Show(TableNames.Count.ToString());
            }
        }

        public static DataTable GetDataTableEmployeeTimeSheet()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM EmployeeTimeSheet";

            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {

                a.Fill(EmployeeTimeSheet);
                //a.Update(Receipts.Select(null, null, DataViewRowState.Added));
            }

            conn.Close();
            return Receipts;
        }

        public static DataTable GetDataTableReceipts()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM Receipt";

            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {

                a.Fill(Receipts);
                //a.Update(Receipts.Select(null, null, DataViewRowState.Added));
            }

            conn.Close();
            return Receipts;
        }

        public static DataTable GetDataTableReceiptsData()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM ReceiptData";

            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {

                a.Fill(ReceiptsData);
            }

            conn.Close();
            return Receipts;
        }

        public static DataTable GetDataTableMenuItems()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM MenuItems";

            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {

                a.Fill(MenuItems);
                
            }

            conn.Close();

            LoadMenuItems();

            return Receipts;
        }

        public static DataTable GetDataTableMenuItemsCategory()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM MenuCategory";
            try
            {
                SqlCommand cmd = new SqlCommand(query, conn);
                using (SqlDataAdapter a = new SqlDataAdapter(cmd))
                {

                    a.Fill(MenuItemsCategory);
                }

            }
            catch(Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            conn.Close();

            LoadMenuCategoryList();

            return Receipts;
        }

        public static DataTable GetDataTableEmployeeLogin()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM EmployeeLogin";

            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                EmployeeLogin = new DataTable("EmployeeLogin");
                a.Fill(EmployeeLogin);
                //a.Update(Receipts.Select(null, null, DataViewRowState.Added));
            }

            conn.Close();
            return Receipts;
        }

        public static DataTable GetDataTableEmployeeTips()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM EmployeeTips";

            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {

                a.Fill(EmployeeTips);
                //a.Update(Receipts.Select(null, null, DataViewRowState.Added));
            }

            conn.Close();
            return Receipts;
        }

        public static DataTable GetDataTableEmployeeSchedule()
        {
            SqlConnection conn = new SqlConnection(myConnectionString);
            conn.Open();

            string query = "SELECT * FROM EmployeeSchedule";

            SqlCommand cmd = new SqlCommand(query, conn);
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {

                a.Fill(EmployeeSchedule);
                //a.Update(Receipts.Select(null, null, DataViewRowState.Added));
            }

            conn.Close();
            return Receipts;
        }

        public static void SaveDataSetToFile()
        {
            if(!POS.Tables.Contains("Receipts"))
            {
            POS.Tables.Add(Receipts);
            }
            //POS.Tables.Add(ReceiptsData);
            // Save to disk
            try
            {
                POS.DataSetName = "POS";
                POS.WriteXml(@"C:\Users\Roger Jenkins\Desktop\MyDataset.xml");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }


        }

        public static void ReadDataSetFromFile()
        {
            // Read from disk
            POS.ReadXml(@"C:\MyDataset.xml");
        }

        public static void LoadMenuItems()
        {
            try
            {
                foreach (DataRow item in MenuItems.Rows)
                {

                    if (!dictionaryCategorys.Keys.Contains(item.Field<string>(1).Trim()))
                    {
                        dictionaryCategorys.Add(item.Field<string>(1).Trim(), item.Field<string>(2).Trim());
                        dictionaryPrices.Add(item.Field<string>(2).Trim(), Convert.ToDouble(item.Field<decimal>(4)));
                    }
                }
            }
            catch (Exception E)
            {
                MessageBox.Show(E.ToString());
            }

            


        }

        public static void LoadMenuCategoryList()
        {
            try
            {
                foreach (DataRow item in MenuItemsCategory.Rows)
                {
                    if (!categoryList.Contains(item.Field<string>(1).Trim()))
                    {
                        categoryList.Add(item.Field<string>(1).Trim());
                    }
                }
            }
            catch (Exception E)
            {
                MessageBox.Show(E.ToString());
            }

            //MessageBox.Show(categoryList.Count.ToString());
        }

        public static bool IsServerConnected()
        {
            using (SqlConnection connection = new SqlConnection(myConnectionString))
            {
                try
                {
                    connection.Open();
                    connection.Close();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }

            }
        }

        public static void SendDataToSqlServer(string tableName, DataTable dataTable)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(myConnectionString))
            {
                bulkCopy.DestinationTableName = tableName;

                try
                {
                    // Write unchanged rows from the source to the destination.
                    bulkCopy.WriteToServer(dataTable, DataRowState.Added);
                    bulkCopy.WriteToServer(dataTable, DataRowState.Modified);
                    dataTable.Rows[0].AcceptChanges();

                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }
}
